<?php
/**
 * Local variables
 * @var \Phalcon\Mvc\Micro $app
 */

/**
 * Add your routes here
 */
$app->get('/', function () {
    $union_id = uniqid();
    $this['view']->union_id = $union_id;
    return $this['view']->render('app/index');
});

$app->post('/add', function () {
    $data = $this->request->get();
    if ($this->request->hasFiles()) {
        $files = $this->request->getUploadedFiles();
        $root = "uploads/excel/" . date("Ymd");
        if (!is_dir($root)) {
            mkdir($root, 0777, true);
        }
        $name = '';
        foreach ($files as $file) {
            $name = $root . '/' . uniqid() . '.' . $file->getExtension();
            $file->moveTo($name);
            $reader = $file->getExtension() == "xls" ? 'Excel5' : 'Excel2007';
            $reader = PHPExcel_IOFactory::createReader($reader);
            $PHPExcel = $reader->load($name); // 载入excel文件
            $sheet = $PHPExcel->getSheet($data['sheet']); // 读取工作表
            $highestRow = $sheet->getHighestRow() > 3000 ? 3000 : $sheet->getHighestRow(); // 取得总行数
            $highestColumm = $sheet->getHighestColumn(); // 取得总列数
            //$highestColumm = "M";

            if (isset($data['is_main']) && $data['is_main'] == 'on') {
                /** 循环读取每个单元格的数据 */
                for ($row = 1; $row <= $highestRow; $row++) {//行数是以第1行开始
                    $bill = new Bill();
                    $bill->union_id = $data['union_id'];
                    $bill->pid = $sheet->getCell(strtoupper($data['pid']) . $row)->getValue();
                    for ($column = 'A'; $column <= $highestColumm; $column++) {//列数是以A列开始
                        $field = 'filed_' . strtolower($column);
                        $bill->$field = $sheet->getCell($column . $row)->getValue();
                    }
                    $bill->save();
                }
            } else {
                $relation = $data['relation'];
                for ($row = 1; $row <= $highestRow; $row++) {//行数是以第1行开始
                    $bill = Bill::findFirst([
                        'conditions' => 'union_id=?0 and pid=?1',
                        'bind' => [
                            $data['union_id'],
                            $sheet->getCell(strtoupper($data['pid']) . $row)->getValue()
                        ],
                    ]);
                    if ($bill) {
                        foreach ($relation as $i => $v) {
                            $rel = explode('-', $v);
                            if (!empty($rel[0]) && !empty($rel[1])) {
                                $field = 'filed_' . strtolower($rel[1]);
                                $bill->$field = $sheet->getCell(strtoupper($rel[0]) . $row)->getValue();
                            }
                        }
                        $bill->save();
                    }
                }
            }
        }
        echo "SUCCESS";
    }
});

$app->get('/build/{union_id}', function ($union_id) {
    $bill = Bill::find([
        'conditions' => 'union_id=?0',
        'bind' => [
            $union_id
        ],
        'order' => 'id asc'
    ]);
    $objPHPExcel = new PHPExcel();
    $objProps = $objPHPExcel->getProperties();

    $objActSheet = $objPHPExcel->getActiveSheet();
    $column = 1;
    $file = 'app/excel/' . uniqid() . ".xls";
    foreach ($bill as $item) { //行写入
        $span = ord("A");
        for ($i = 'a'; $i < 'z'; $i++) {// 列写入
            $field = 'filed_' . strtolower($i);
            if (isset($item->$field) && !empty($item->$field)) {
                $objActSheet->setCellValue(strtoupper($i) . $column, $item->$field);
            }
        }
        $column++;
    }
    //设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
    $objWriter->save($file);

    $this['view']->url = "/" . $file;
    return $this['view']->render('app/download');
});

/**
 * Not found handler
 */
$app->notFound(function () use ($app) {
    $app->response->setStatusCode(404, "Not Found")->sendHeaders();
    echo $app['view']->render('404');
});
